package common.main.sougouWeixin;

import java.sql.*;
import java.util.List;

public class SougouWeixinSaveDataToSql{

    protected static Connection conn = null;
    protected static Statement stmt = null;


    public static void connectDatabase() throws ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException {

        Class.forName("com.mysql.jdbc.Driver").newInstance();
        String url ="jdbc:mysql://"+SougouWeixinConfig.mysqlHost +"/"
                +SougouWeixinConfig.mysqlDatabaseName+"?user="+SougouWeixinConfig.mysqlUserName+"&password="+SougouWeixinConfig.mysqlPassword+"&useUnicode=true&characterEncoding=UTF-8";
        if(conn == null || conn.isClosed()){
            conn = DriverManager.getConnection(url);
        }
        if(stmt == null || stmt.isClosed()){
            stmt = conn.createStatement();
        }
    }

    public static String getAllMd5(){
        try{
            connectDatabase();
            String sql = "select MD5 from weixin_data";
            ResultSet rs = stmt.executeQuery(sql);
            StringBuilder sb = new StringBuilder();
            while (rs.next()){
                sb.append(rs.getString("MD5") + ",");
            }
            return sb.toString();
        }catch (Exception e){
            e.printStackTrace();
            return "";
        }
    }

    public static void saveDataToSql(List<SougouWeixinData> list){

        try{
            connectDatabase();
            String modelSql = "insert into weixin_data (TITLE,AUTHOR,PUBTIME,URL,SEARCH_KEYWORD,SOURCE,MD5,CONTENT,BRIEF,IMG_URL)" +
                    "VALUES ('<title>','<author>','<pubTime>','<url>','<searchKeyword>','<source>','<md5>','<content>','<brief>','<imgUrl>')";
            for(int i = 0;i < list.size();i++) {
                SougouWeixinData data = list.get(i);

                data.setTitle(data.getTitle().replace("'", "\\'").trim());
                data.setAuthor(data.getAuthor().replace("'", "\\'").trim());
                data.setUrl(data.getUrl().replace("'", "\\'").trim());
                data.setSource(data.getSource().replace("'", "\\'").trim());
                data.setContent(data.getContent().replace("'", "\\'").trim());
                data.setBrief(data.getBrief().replace("'", "\\'").trim());
                data.setImgUrl(data.getImgUrl().replace("'", "\\'").trim());


                String sql = modelSql.replace("<title>", data.getTitle())
                        .replace("<author>", data.getAuthor())
                        .replace("<pubTime>", data.getPubTime())
                        .replace("<url>", data.getUrl())
                        .replace("<searchKeyword>", data.getSearchKeword())
                        .replace("<source>", data.getSource())
                        .replace("<md5>", data.getMd5())
                        .replace("<content>", data.getContent())
                        .replace("<brief>", data.getBrief())
                        .replace("<imgUrl>", data.getImgUrl());

                try{
                    stmt.executeUpdate(sql);
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }catch(Exception e){
            e.printStackTrace();
        }
        finally {
            try{
                stmt.close();
                conn.close();
            }catch (Exception e){

            }
        }
    }
}
